package org.ehe.business.bom.service.impl;

import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.ehe.business.bom.service.ExcelTemplateService;
import org.ehe.common.satoken.utils.LoginHelper;
import org.ehe.erp.domain.product.ErpProductCategory;
import org.ehe.erp.domain.product.bo.ErpProductUnitBo;
import org.ehe.erp.domain.product.vo.ErpMaterialVo;
import org.ehe.erp.domain.product.vo.ErpProductUnitVo;
import org.ehe.erp.service.product.IErpProductCategoryService;
import org.ehe.erp.service.product.IErpProductUnitService;
import org.ehe.erp.service.product.MaterialService;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * excel模版实现类
 * @author :zhangnn
 * @className :ExcelTemplateServiceImpl
 * @description: TODO
 * @date 2025-09-09 13:52:23
 */
@Service
@Slf4j
@RequiredArgsConstructor
public class ExcelTemplateServiceImpl implements ExcelTemplateService {

    private final IErpProductCategoryService categoryService;
    private final MaterialService materialService;
    private final IErpProductUnitService unitService;
    // private final IWorkPositionService workPositionService;

    /**
     * 生成带下拉选项的Excel模板
     */
    public void generateExcelTemplate(HttpServletResponse response,String fileName) {
        Workbook workbook = null;
        try {
            InputStream tpl = getClass().getResourceAsStream("/excel/template.xlsm");

            workbook = new XSSFWorkbook(tpl);
            int idx = workbook.getSheetIndex("Sheet1");
            if (idx != -1) {
                workbook.removeSheetAt(idx);
            }

            // 1. 创建主工作表, 并将其从 "BOM信息" 重命名为 "物料信息"
            Sheet mainSheet = workbook.getSheet("BOM信息");
            workbook.setSheetName(workbook.getSheetIndex(mainSheet), "物料信息");

            // 创建隐藏的数据工作表
            Sheet dataSheet = workbook.createSheet("Data");
            workbook.setSheetHidden(1, false);

            // 设置主工作表表头
            createMainSheetHeader(mainSheet);

            // 获取数据库数据
            List<ErpProductCategory> categories = categoryService.findActiveCategories(LoginHelper.getTenantId());
            List<ErpMaterialVo> materials = materialService.getAllEnabledMaterials(LoginHelper.getTenantId());
            ErpProductUnitBo bo = new ErpProductUnitBo();
            List<ErpProductUnitVo> units = unitService.queryList(bo);
            // 2. 获取 "工位" 数据
            // List<String> workPositions = workPositionService.getAllWorkPositions();
            List<String> workPositions = Arrays.asList("工位A", "工位B", "工位C", "工位D");
            // 3. 在数据源工作表中填充数据, 包含新增的 "工位" 数据
            int[] dataPositions = fillDataSheet(dataSheet, categories, materials, units, workPositions);

            // 关键：保护Data工作表，设置为只读
            protectDataSheet(dataSheet);

            // 设置 "标准件" 表的数据验证（下拉选项）
            setDataValidation(workbook, mainSheet, dataPositions);

            // ==================== 注销掉非标件 2025.09.28 START ====================
            // 4. 创建 "非标件" 工作表并设置其数据验证
            //createNonStandardSheet(workbook, dataPositions);
            // ==================== 注销掉非标件 2025.09.28 END ======================
            // 设置响应头 - 在写入数据之前设置
            setResponseHeaders(response, fileName);

            // 输出到响应流
            try (ServletOutputStream outputStream = response.getOutputStream()) {
                workbook.write(outputStream);
                outputStream.flush();
            }
        } catch (Exception e) {
            log.error("生成Excel模板失败", e);
            // 如果已经开始写入响应，则无法重置，只能记录错误
            if (!response.isCommitted()) {
                response.reset();
                response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
                try {
                    response.getWriter().write("生成Excel模板失败: " + e.getMessage());
                } catch (IOException ioException) {
                    log.error("写入错误响应失败", ioException);
                }
            }
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    log.error("关闭workbook失败", e);
                }
            }
        }
    }

    /**
     * 将字符串转换为可用于Excel命名区域的合法名称。
     * 替换所有非法字符为下下划线。
     * @param name 原始名称
     * @return 清理后的名称
     */
    private String sanitizeNameForExcel(String name) {
        if (name == null || name.isEmpty()) {
            return "_";
        }
        // Excel命名规则：不能以数字开头，不能包含空格和大多数标点符号。
        // 这里我们用一个简单的策略：将所有非字母、非数字、非中文字符替换为下划线。
        String sanitized = name.replaceAll("[^a-zA-Z0-9\\u4e00-\\u9fa5]", "_");

        // 如果以数字开头，在前面加上下划线
        if (Character.isDigit(sanitized.charAt(0))) {
            sanitized = "_" + sanitized;
        }
        return sanitized;
    }

    /**
     * 创建主工作表表头
     */
    private void createMainSheetHeader(Sheet sheet) {
        Row headerRow = sheet.createRow(0);

        // 创建表头样式
        CellStyle headerStyle = sheet.getWorkbook().createCellStyle();
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);

        Font headerFont = sheet.getWorkbook().createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);

        // 设置表头
        String[] headers = {
            "产品名称*",
            "种类*", "大类*", "次分类*", "细类*", "最后类*",
            "材料*", "单位*", "规格*", "备注"
        };

        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
            sheet.setColumnWidth(i, 4000); // 设置列宽
        }

        // 创建说明行
        Row descRow = sheet.createRow(1);
        Cell descCell = descRow.createCell(0);

        // 1. 创建默认字体（黑色）
        Workbook workbook = sheet.getWorkbook();
        Font defaultFont = workbook.createFont();
        defaultFont.setColor(Font.COLOR_NORMAL); // 黑色（默认）

        // 2. 创建红色字体
        Font redFont = workbook.createFont();
        redFont.setColor(Font.COLOR_RED); // 红色

        // 3. 构建富文本字符串
        RichTextString richText = new XSSFRichTextString("请在下方填写产品信息，带*为必填项（勿删此行）");

        // 4. 设置"*"为红色（找到"*"的位置并应用红色字体）
//        int starIndex = richText.getString().indexOf("*");
//        if (starIndex != -1) {
//            richText.applyFont(starIndex, starIndex + 1, redFont);
//        }

        // 5. 设置"勿删此行"为红色（找到对应子串的位置）
        String targetText = "勿删此行";
        int startIndex = richText.getString().indexOf(targetText);
        if (startIndex != -1) {
            int endIndex = startIndex + targetText.length();
            richText.applyFont(startIndex, endIndex, redFont);
        }

        // 6. 将富文本设置到单元格
        descCell.setCellValue(richText);

        CellStyle descStyle = sheet.getWorkbook().createCellStyle();
        Font descFont = sheet.getWorkbook().createFont();
        descFont.setColor(IndexedColors.BLUE.getIndex());
        descStyle.setFont(descFont);
        descCell.setCellStyle(descStyle);

        // 合并说明单元格
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, headers.length - 1));
    }


    // ==================== MODIFICATION START ====================
    /**
     * 在数据源工作表中填充数据，并按父分类名称组织，以支持级联下拉。
     */
    private int[] fillDataSheet(Sheet dataSheet,
                                List<ErpProductCategory> categories,
                                List<ErpMaterialVo> materials,
                                List<ErpProductUnitVo> units,
                                List<String> workPositions) {

        // 1. 按 parentId 分组
        Map<Long, List<ErpProductCategory>> parentMap = categories.stream()
            .collect(Collectors.groupingBy(ErpProductCategory::getParentId,
                LinkedHashMap::new, Collectors.toList()));

        // 2. 按 id 索引，方便查找父分类名称
        Map<Long, ErpProductCategory> idMap = categories.stream()
            .collect(Collectors.toMap(ErpProductCategory::getId, c -> c, (k1, k2) -> k1));

        Row headerRow = dataSheet.getRow(0);
        if (headerRow == null) headerRow = dataSheet.createRow(0);

        // 3. 写入 ROOT 列（顶级分类）
        headerRow.createCell(0).setCellValue("ROOT");
        List<ErpProductCategory> roots = parentMap.getOrDefault(0L, List.of());
        int r = 1;
        for (ErpProductCategory c : roots) {
            Row dataRow = dataSheet.getRow(r);
            if (dataRow == null) dataRow = dataSheet.createRow(r);
            dataRow.createCell(0).setCellValue(c.getName());
            r++;
        }
        int maxRow = r - 1;

        // 4. 写入所有子分类列，列头为父分类的名称
        int col = 1;
        for (Map.Entry<Long, List<ErpProductCategory>> entry : parentMap.entrySet()) {
            Long parentId = entry.getKey();
            if (parentId == 0L) continue; // 跳过顶级分类

            ErpProductCategory parentCategory = idMap.get(parentId);
            if (parentCategory == null) continue; // 如果找不到父分类，则跳过

            // 使用清理后的父分类名称作为列头
            headerRow.createCell(col).setCellValue(parentCategory.getName());

            List<ErpProductCategory> children = entry.getValue();
            int rowIdx = 1;
            for (ErpProductCategory child : children) {
                Row dataRow = dataSheet.getRow(rowIdx);
                if (dataRow == null) dataRow = dataSheet.createRow(rowIdx);
                dataRow.createCell(col).setCellValue(child.getName());
                rowIdx++;
            }
            maxRow = Math.max(maxRow, rowIdx - 1);
            col++;
        }

        // 5. 写入材料和单位
        int matCol = col;
        headerRow.createCell(matCol).setCellValue("材料");
        int mr = 1;
        for (ErpMaterialVo m : materials) {
            Row dataRow = dataSheet.getRow(mr);
            if (dataRow == null) dataRow = dataSheet.createRow(mr);
            dataRow.createCell(matCol).setCellValue(m.getMaterialName());
            mr++;
        }

        int unitCol = matCol + 1;
        headerRow.createCell(unitCol).setCellValue("单位");
        int ur = 1;
        for (ErpProductUnitVo u : units) {
            Row dataRow = dataSheet.getRow(ur);
            if (dataRow == null) dataRow = dataSheet.createRow(ur);
            dataRow.createCell(unitCol).setCellValue(u.getName());
            ur++;
        }

        // 6. 写入工位
        int workPosCol = unitCol + 1;
        headerRow.createCell(workPosCol).setCellValue("工位");
        int wr = 1;
        for (String wp : workPositions) {
            Row dataRow = dataSheet.getRow(wr);
            if (dataRow == null) dataRow = dataSheet.createRow(wr);
            dataRow.createCell(workPosCol).setCellValue(wp);
            wr++;
        }


        return new int[]{
            col - 1, // 最后一个分类列的索引
            Math.max(Math.max(maxRow, mr - 1), Math.max(ur - 1, wr - 1)), // 最大行数
            matCol,
            unitCol,
            workPosCol
        };
    }
    // ==================== MODIFICATION END ======================

    /**
     * 设置数据验证，实现级联下拉
     */
    private void setDataValidation(Workbook workbook, Sheet mainSheet, int[] arr) {
        DataValidationHelper helper = mainSheet.getDataValidationHelper();
        Sheet dataSheet = workbook.getSheet("Data");
        int maxDataRow = arr[1] + 1;

        // 1. 为Data工作表的每一列创建全局命名区域
        int categoryCols = arr[0] + 1;
        for (int c = 0; c < categoryCols; c++) {
            Cell headerCell = dataSheet.getRow(0).getCell(c);
            if (headerCell == null || headerCell.getStringCellValue().isEmpty()) continue;
            String headerName = headerCell.getStringCellValue();
            String validName = sanitizeNameForExcel(headerName);
            Name namedRange = workbook.createName();
            namedRange.setNameName(validName);
            String columnLetter = CellReference.convertNumToColString(c);
            String formula = String.format("Data!$%s$2:$%s$%d", columnLetter, columnLetter, maxDataRow);
            namedRange.setRefersToFormula(formula);
        }

        // 2. 设置 "种类" (B列) 的下拉列表
        CellRangeAddressList kindRange = new CellRangeAddressList(2, 1000, 1, 1);
        DataValidationConstraint kindConstraint = helper.createFormulaListConstraint(sanitizeNameForExcel("ROOT"));
        DataValidation kindValidation = helper.createValidation(kindConstraint, kindRange);
        kindValidation.setSuppressDropDownArrow(true);
        kindValidation.setShowErrorBox(true);
        mainSheet.addValidationData(kindValidation);

        // 3. 设置 "大类" 到 "最后类" (C列到F列) 的级联下拉列表
        for (int col = 2; col <= 5; col++) {
            CellRangeAddressList range = new CellRangeAddressList(2, 1000, col, col);
            String prevColLetter = CellReference.convertNumToColString(col - 1);

            // 引用前一列的第一个数据单元格, Excel会自动调整行号
            String cellRef = prevColLetter + "3";

            // 构建一个能处理数字开头的 INDIRECT 公式
            // ISNUMBER(--LEFT(cell,1)) 是判断单元格第一个字符是否为数字的技巧
            // IF(判断结果, "_&" & cell, cell) 如果是数字开头，就在前面加上"_", 否则不变
            String formula = "INDIRECT(" +
                "IF(" +
                "ISNUMBER(--LEFT(" + cellRef + ",1))," +
                "\"_\" & " + cellRef + "," +
                cellRef +
                ")" +
                ")";

            DataValidationConstraint constraint = helper.createFormulaListConstraint(formula);
            DataValidation validation = helper.createValidation(constraint, range);
            validation.setSuppressDropDownArrow(true);
            validation.setShowErrorBox(true);
            mainSheet.addValidationData(validation);
        }

        // 4. 设置 "材料" 和 "单位" 的下拉列表
        CellRangeAddressList mRange = new CellRangeAddressList(2, 1000, 6, 6);
        String mColLetter = CellReference.convertNumToColString(arr[2]);
        String mFormula = String.format("Data!$%s$2:$%s$%d", mColLetter, mColLetter, maxDataRow);
        DataValidation mValidation = helper.createValidation(helper.createFormulaListConstraint(mFormula), mRange);
        mValidation.setSuppressDropDownArrow(true);
        mainSheet.addValidationData(mValidation);

        CellRangeAddressList uRange = new CellRangeAddressList(2, 1000, 7, 7);
        String uColLetter = CellReference.convertNumToColString(arr[3]);
        String uFormula = String.format("Data!$%s$2:$%s$%d", uColLetter, uColLetter, maxDataRow);
        DataValidation uValidation = helper.createValidation(helper.createFormulaListConstraint(uFormula), uRange);
        uValidation.setSuppressDropDownArrow(true);
        uValidation.setShowErrorBox(true);
        mainSheet.addValidationData(uValidation);
    }

    /**
     *
     * 创建 "非标件" 工作表, 包括其表头和数据验证 (下拉列表)
     * @param workbook
     * @param dataPositions
     */
    private void createNonStandardSheet(Workbook workbook, int[] dataPositions) {
        Sheet nonStandardSheet = workbook.createSheet("非标件");
        DataValidationHelper helper = nonStandardSheet.getDataValidationHelper();

        // --- 1.创建表头
        Row headerRow = nonStandardSheet.createRow(0);
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);

        String[] headers = {"物料名称*", "工位*", "材料*", "单位*", "规格*", "数量", "备注"};
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
            nonStandardSheet.setColumnWidth(i, 4000);
        }

        // --- 2. 设置数据验证
        int maxDataRow = dataPositions[1] + 1;
        int materialColIdx = dataPositions[2];
        int unitColIdx = dataPositions[3];
        int workPosColIdx = dataPositions[4];

        // "工位" 下拉菜单 (Column B, index 1)
        CellRangeAddressList workPosRange = new CellRangeAddressList(1, 1000, 1, 1);
        String workPosColLetter = CellReference.convertNumToColString(workPosColIdx);
        String workPosFormula = String.format("Data!$%s$2:$%s$%d", workPosColLetter, workPosColLetter, maxDataRow);
        DataValidation workPosValidation = helper.createValidation(helper.createFormulaListConstraint(workPosFormula), workPosRange);
        workPosValidation.setSuppressDropDownArrow(true);
        workPosValidation.setShowErrorBox(true);
        nonStandardSheet.addValidationData(workPosValidation);

        // "材料" 下拉菜单 (Column C, index 2)
        CellRangeAddressList materialRange = new CellRangeAddressList(1, 1000, 2, 2);
        String materialColLetter = CellReference.convertNumToColString(materialColIdx);
        String materialFormula = String.format("Data!$%s$2:$%s$%d", materialColLetter, materialColLetter, maxDataRow);
        DataValidation materialValidation = helper.createValidation(helper.createFormulaListConstraint(materialFormula), materialRange);
        materialValidation.setSuppressDropDownArrow(true);
        materialValidation.setShowErrorBox(true);
        nonStandardSheet.addValidationData(materialValidation);

        // "单位" 下拉菜单 (Column D, index 3)
        CellRangeAddressList unitRange = new CellRangeAddressList(1, 1000, 3, 3);
        String unitColLetter = CellReference.convertNumToColString(unitColIdx);
        String unitFormula = String.format("Data!$%s$2:$%s$%d", unitColLetter, unitColLetter, maxDataRow);
        DataValidation unitValidation = helper.createValidation(helper.createFormulaListConstraint(unitFormula), unitRange);
        unitValidation.setSuppressDropDownArrow(true);
        unitValidation.setShowErrorBox(true);
        nonStandardSheet.addValidationData(unitValidation);
    }

    /**
     * 设置HTTP响应头
     */
    private void setResponseHeaders(HttpServletResponse response, String filename) {
        response.setContentType("application/vnd.ms-excel.sheet.macroEnabled.12");
        response.setCharacterEncoding("UTF-8");

        // 1. 直接忽略外面传的后缀，锁死 .xlsm
        String finalFilename = (filename == null || filename.isBlank() ? "BOM信息导入模版" : filename.replaceAll("\\.\\w+$", "")) + ".xlsm";
        System.out.println("finalFilename=====>"+finalFilename);
        try {
            String encodedFilename = URLEncoder.encode(finalFilename, "UTF-8").replace("+", "%20");
            System.out.println("encodedFilename=====>"+encodedFilename);

            response.setHeader("Content-Disposition",
                "attachment; filename=\"" + encodedFilename + "\"; filename*=UTF-8''" + encodedFilename);
        } catch (UnsupportedEncodingException e) {
            response.setHeader("Content-Disposition", "attachment; filename=\"template.xlsm\"");
        }

        response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Expires", "0");
    }

    /**
     * 保护Data工作表，禁止编辑其中的数据
     * @param dataSheet 需要保护的Data工作表
     */
    private void protectDataSheet(Sheet dataSheet) {
        // 1. 设置保护密码（null表示无密码保护，但禁止编辑）
        String password = "eiahe@123";

        // 2. 对XSSFSheet（.xlsx格式）应用保护
        if (dataSheet instanceof XSSFSheet) {
            XSSFSheet xssfSheet = (XSSFSheet) dataSheet;
            // 设置保护选项（通过CTSheetProtection，POI底层对象）
            org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheetProtection protection = xssfSheet.getCTWorksheet().getSheetProtection();
            if (protection == null) {
                protection = xssfSheet.getCTWorksheet().addNewSheetProtection();
            }
            // 允许选择单元格，但禁止编辑
            protection.setSelectLockedCells(true);
            protection.setSelectUnlockedCells(true);
            // 禁止所有编辑操作
            protection.setFormatCells(false);
            protection.setFormatColumns(false);
            protection.setFormatRows(false);
            protection.setInsertColumns(false);
            protection.setInsertRows(false);
            protection.setDeleteColumns(false);
            protection.setDeleteRows(false);
            // 应用保护（设置密码）
            xssfSheet.protectSheet(password);
        } else {
            // 对HSSFSheet（.xls格式）的兼容处理
            dataSheet.protectSheet(password);
        }

        // 3. 确保所有单元格处于锁定状态（配合保护生效）
        CellStyle lockedStyle = dataSheet.getWorkbook().createCellStyle();
        lockedStyle.setLocked(true); // 锁定单元格

        // 遍历所有行和单元格，应用锁定样式
        for (int rowNum = 0; rowNum <= dataSheet.getLastRowNum(); rowNum++) {
            Row row = dataSheet.getRow(rowNum);
            if (row == null) continue;
            for (int cellNum = 0; cellNum < row.getLastCellNum(); cellNum++) {
                Cell cell = row.getCell(cellNum);
                if (cell == null) {
                    cell = row.createCell(cellNum);
                }
                cell.setCellStyle(lockedStyle);
            }
        }
    }

    /**
     * 为下拉单元格设置特殊样式，增强下拉提示视觉效果
     */
    private void setCellStyleWithDropdownHint(Sheet sheet, int startRow, int endRow, int column) {
        CellStyle style = sheet.getWorkbook().createCellStyle();
        // 添加浅灰色背景，提示用户该单元格有下拉选项
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 为指定范围的单元格应用样式
        for (int rowNum = startRow; rowNum <= endRow; rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) {
                row = sheet.createRow(rowNum);
            }
            Cell cell = row.getCell(column);
            if (cell == null) {
                cell = row.createCell(column);
            }
            cell.setCellStyle(style);
        }
    }
}
